import { ResultSetHeader, RowDataPacket } from 'mysql2';
import pool from '../config/db';
import { promises } from 'dns';
import bcrypt from 'bcryptjs';  // 用于验证密码
import { v4 as uuidv4 } from 'uuid';

interface User {
    id?: number;
    username: string;
    openid?: string;
    session_key?: string;
    // email: string;
    phone: string;
    password: string;
    created_at: any;
    family_id: any;
}

// 获取所有用户
export const getAllUsers = async (): Promise<User[]> => {
    const [rows] = await pool.query<RowDataPacket[]>('SELECT * FROM users');
    return rows as User[];
};

// 根据ID获取用户
export const getUserById = async (id: number): Promise<User | null> => {
    const [rows] = await pool.query<RowDataPacket[]>('SELECT * FROM users WHERE id = ?', [id]);
    return rows.length ? (rows[0] as User) : null;
};

// 根据 openid 查询用户 
export const getUserByOpenid = async (openid: string): Promise<User | null> => {
    const [rows] = await pool.query<RowDataPacket[]>('SELECT * FROM users WHERE openid = ?', [openid]);
    return rows.length ? (rows[0] as User) : null;
};

// 微信登录注册用户 
export const WeChatCreatUser = async (openid: string, session_key: string, created_at: Date): Promise<User> => {
    if (!openid || !session_key || !created_at) {
        throw new Error('缺少必要的用户信息');
    }

    try {
        // 将时间转换为 MySQL 支持的格式 'YYYY-MM-DD HH:MM:SS'
        const formattedDate = created_at.toISOString().slice(0, 19).replace('T', ' ');

        // 生成 UUID 作为 family_id
        const family_id = uuidv4();

        console.log('执行创建操作');
        // 执行插入操作
        const [result] = await pool.query<ResultSetHeader>(
            'INSERT INTO users (openid, session_key, created_at, family_id) VALUES (?, ?, ?, ?)',
            [
                openid,
                session_key,
                formattedDate,  // 使用格式化后的日期
                family_id       // 插入生成的 UUID
            ]
        );

        // 插入数据库后，检查插入是否成功
        if (result.affectedRows === 0) {
            throw new Error('数据库插入失败');
        } else {
            // 创建新用户对象，并赋值 ID 和其他字段
            const newUser: User = {
                id: result.insertId, // 获取插入的 ID
                openid,
                session_key,
                created_at: formattedDate,  // 使用格式化后的日期
                username: '',
                phone: '',
                password: '',
                family_id // 将生成的 UUID 返回
            };

            return newUser;
        }
    } catch (err) {
        console.error('创建用户时发生错误:', err);
        throw new Error('用户创建失败');
    }
};

// 注册用户 
export const createUser = async (user: User): Promise<User> => {
    // 定义一个哈希密码
    const hashedPassword = await bcrypt.hash(user.password, 10);

    // 插入用户数据
    const [result] = await pool.query<ResultSetHeader>(
        'INSERT INTO users (username, phone, password, openid, session_key, created_at,family_id) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [
            user.username,
            user.phone,
            hashedPassword, // 确保密码哈希值插入到 password 字段
            user.openid,
            user.session_key,
            user.created_at || new Date(), // 使用用户提供的 created_at 或当前时间
            user.family_id,
        ]
    );

    // 如果没有插入成功
    if (result.affectedRows === 0) {
        throw new Error('数据库插入失败');
    }

    // 获取插入的 ID
    user.id = result.insertId;
    return user;
};

// 根据手机号查询用户 
export const findUserByPhone = async (phone: string): Promise<User | null> => {
    const [rows] = await pool.query<RowDataPacket[]>('SELECT * FROM USERS WHERE phone = ?', [phone]);
    return rows.length ? (rows[0] as User) : null
}

// 验证密码 
export const validateUserPassword = async (User: User, password: string): Promise<Boolean> => {
    return bcrypt.compare(password, User.password)
}

// 根据famliy_id 查询用户列表 
export const getUserByFamilyId = async (family_id: string): Promise<User[]> => {
    const [rows] = await pool.query<RowDataPacket[]>('SELECT * FROM users WHERE family_id = ?', [family_id]);
    return rows.map((row) => ({
        id: row.id,
        username: row.username,
        openid: row.openid,
        session_key: row.session_key,
        phone: row.phone,
        password: row.password,
        created_at: row.created_at,
        family_id: row.family_id
    }));
};

// 根据手机号添加成员(查询到用户存在后修改family_id的值)
export const addFamilyUsersByPhone = async (phone: string, family_id: string): Promise<User[] | string> => {
    // 先根据手机号查找是否存在用户
    const user = await findUserByPhone(phone);
    // 如果用户不存在，返回“手机号还没有注册”
    if (!user) {
        return `手机号 ${phone} 还没有注册`; // 返回字符串，提示手机号没有注册
    }
    // 如果用户存在，更新 family_id
    const updateQuery = 'UPDATE USERS SET family_id = ? WHERE phone = ?';
    await pool.query(updateQuery, [family_id, phone]);
    // 查询更新后的用户信息
    const updatedUser = await findUserByPhone(phone);
    // 检查 updatedUser 是否为 null
    if (!updatedUser) {
        throw new Error("Failed to update user");
    }
    return [updatedUser]; // 返回更新后的用户信息
};
